﻿/**********************************************************************
 * 
 *   Sample of using C#4.0 to non-interactively manipulate Excel 2010
 *      to generate and export chart
 *      
 *   C#4.0 后台操作Excel 2010生成并导出图表完整实例代码
 * 
 *   Make sure install VS2010 and Office2010 before 
 *      compile and run this sample code
 *
 *   请首先安装VS2010及Office2010，然后方可编译并运行
 *   
 *   Author:
 *      stainboyx@hotmail.com
 *      
 *   References:
 *      http://csharp.net-informations.com/excel/csharp-excel-chart.htm
 *      http://csharp.net-informations.com/excel/csharp-excel-chart-export.htm
 * 
 **********************************************************************/

// Import C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.Excel.dll
// Import C:\Program Files\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\Profile\Client\System.Drawing.dll


using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Imaging;
using System.Linq;
using System.Text;

namespace InteropExcelChart
{
    class Program
    {
        static void Main(string[] args)
        {
            Microsoft.Office.Interop.Excel.Application excel =
                new Microsoft.Office.Interop.Excel.ApplicationClass();
            var book = excel.Workbooks.Add();

            CreateChart(book, Microsoft.Office.Interop.Excel.XlChartType.xlRadar);
            CreateChart(book, Microsoft.Office.Interop.Excel.XlChartType.xl3DBarClustered);
            CreateChart(book, Microsoft.Office.Interop.Excel.XlChartType.xlLine);
            CreateChart(book, Microsoft.Office.Interop.Excel.XlChartType.xl3DPie);


            book.Close(false);
            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        }

        private static void CreateChart(Microsoft.Office.Interop.Excel.Workbook book, Microsoft.Office.Interop.Excel.XlChartType type)
        {
            var sheet = book.Sheets.Add() as Microsoft.Office.Interop.Excel.Worksheet;
            var chart = book.Charts.Add() as Microsoft.Office.Interop.Excel.Chart;

            // Setting chart type
            chart.ChartType = type;

            sheet.Cells[1, 1] = "";
            sheet.Cells[1, 2] = "张三";
            sheet.Cells[1, 3] = "李四";
            sheet.Cells[1, 4] = "王五";

            sheet.Cells[2, 1] = "语文";
            sheet.Cells[2, 2] = "80";
            sheet.Cells[2, 3] = "65";
            sheet.Cells[2, 4] = "45";

            sheet.Cells[3, 1] = "数学";
            sheet.Cells[3, 2] = "78";
            sheet.Cells[3, 3] = "72";
            sheet.Cells[3, 4] = "60";

            sheet.Cells[4, 1] = "英语";
            sheet.Cells[4, 2] = "82";
            sheet.Cells[4, 3] = "80";
            sheet.Cells[4, 4] = "65";

            sheet.Cells[5, 1] = "地理";
            sheet.Cells[5, 2] = "75";
            sheet.Cells[5, 3] = "82";
            sheet.Cells[5, 4] = "68";

            var range = sheet.Range["A1", "D5"];

            chart.ChartWizard(range, Title: "学生成绩图");


            ExportAndShowImage(chart);

            chart.Delete();
            sheet.Delete();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(chart);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
        }

        private static void ExportAndShowImage(Microsoft.Office.Interop.Excel.Chart chart)
        {
            //
            var name = Environment.CurrentDirectory + @"\" + Guid.NewGuid().ToString() + ".png";
            if (chart.Export(name, "png"))
                Process.Start(name);
            else
                Console.WriteLine("Failed to export!");
            //
        }
    }
}
